﻿namespace PublicStaticClass
{
    using Microsoft.Office.Interop.Excel;
    using System;
    using System.Reflection;
    using System.Runtime.InteropServices;

    public class ExportBaseExcel
    {
        private Border m_BorderBottom;
        private Border m_BorderLeft;
        private Border m_BorderRight;
        private Borders m_Borders;
        private Border m_BorderTop;
        private Range m_cellRange;
        private string[] m_colString = new string[] { 
            "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", 
            "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
         };
        private Font m_Font;
        private Workbook m_objBook;
        private Workbooks m_objBooks;
        private Application m_objExcel = new ApplicationClass();
        private Worksheet m_objSheet;
        private Range m_Range;
        private Missing miss = Missing.Value;

        public ExportBaseExcel()
        {
            this.m_objBooks = this.m_objExcel.Workbooks;
            this.m_objBook = this.m_objBooks.Add(this.miss);
            this.m_objSheet = (Worksheet) this.m_objBook.ActiveSheet;
        }

        ~ExportBaseExcel()
        {
            if (this.m_cellRange != null)
            {
                Marshal.ReleaseComObject(this.m_cellRange);
            }
            if (this.m_BorderTop != null)
            {
                Marshal.ReleaseComObject(this.m_BorderTop);
            }
            if (this.m_BorderBottom != null)
            {
                Marshal.ReleaseComObject(this.m_BorderBottom);
            }
            if (this.m_BorderLeft != null)
            {
                Marshal.ReleaseComObject(this.m_BorderLeft);
            }
            if (this.m_BorderRight != null)
            {
                Marshal.ReleaseComObject(this.m_BorderRight);
            }
            if (this.m_Borders != null)
            {
                Marshal.ReleaseComObject(this.m_Borders);
            }
            if (this.m_Font != null)
            {
                Marshal.ReleaseComObject(this.m_Font);
            }
            if (this.m_Range != null)
            {
                Marshal.ReleaseComObject(this.m_Range);
            }
            if (this.m_objSheet != null)
            {
                Marshal.ReleaseComObject(this.m_objSheet);
            }
            if (this.m_objBook != null)
            {
                Marshal.ReleaseComObject(this.m_objBook);
            }
            if (this.m_objBooks != null)
            {
                Marshal.ReleaseComObject(this.m_objBooks);
            }
            if (this.m_objExcel != null)
            {
            }
            GC.Collect();
        }

        private string GetCell(int ColNum, int RowNum)
        {
            int num = RowNum + 1;
            if ((ColNum < 0) || (ColNum > 0xff))
            {
                throw new Exception("行号错误");
            }
            int result = 0;
            int index = Math.DivRem(ColNum, 0x19, out result);
            if ((index == 0) && (result == 0))
            {
                return ("A" + num.ToString());
            }
            if ((index == 0) && (result > 0))
            {
                return (this.m_colString[result] + num.ToString());
            }
            return (this.m_colString[index] + this.m_colString[result] + num.ToString());
        }

        public object GetText(int row, int col)
        {
            if (this.m_objSheet != null)
            {
                return this.m_objSheet.get_Range(this.GetCell(col, row), this.miss).Text;
            }
            return "";
        }

        public object GetValue(int row, int col)
        {
            if (this.m_objSheet != null)
            {
                return this.m_objSheet.get_Range(this.GetCell(col, row), this.miss).Value2;
            }
            return "";
        }

        public void Merge()
        {
            this.m_Range.Merge(null);
        }

        public void Merge(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
        {
            this.SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
            this.m_Range.Merge(null);
        }

        public void OpenExcelFile(string filename)
        {
            if (this.m_objExcel != null)
            {
                this.m_objExcel = new ApplicationClass();
            }
            this.UserControl(false);
            this.m_objExcel.Workbooks.Open(filename, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss, this.miss);
            this.m_objBooks = this.m_objExcel.Workbooks;
            this.m_objBook = this.m_objExcel.ActiveWorkbook;
            this.m_objSheet = (Worksheet) this.m_objBook.ActiveSheet;
        }

        public void ReleaseExcel()
        {
            if (this.m_cellRange != null)
            {
                Marshal.ReleaseComObject(this.m_cellRange);
            }
            if (this.m_BorderTop != null)
            {
                Marshal.ReleaseComObject(this.m_BorderTop);
            }
            if (this.m_BorderBottom != null)
            {
                Marshal.ReleaseComObject(this.m_BorderBottom);
            }
            if (this.m_BorderLeft != null)
            {
                Marshal.ReleaseComObject(this.m_BorderLeft);
            }
            if (this.m_BorderRight != null)
            {
                Marshal.ReleaseComObject(this.m_BorderRight);
            }
            if (this.m_Borders != null)
            {
                Marshal.ReleaseComObject(this.m_Borders);
            }
            if (this.m_Font != null)
            {
                Marshal.ReleaseComObject(this.m_Font);
            }
            if (this.m_Range != null)
            {
                Marshal.ReleaseComObject(this.m_Range);
            }
            if (this.m_objSheet != null)
            {
                Marshal.ReleaseComObject(this.m_objSheet);
            }
            if (this.m_objBook != null)
            {
                Marshal.ReleaseComObject(this.m_objBook);
            }
            if (this.m_objBooks != null)
            {
                Marshal.ReleaseComObject(this.m_objBooks);
            }
            if (this.m_objExcel != null)
            {
                this.m_objExcel.Quit();
            }
            GC.Collect();
        }

        public void SaveAs(string FileName)
        {
            this.m_objBook.SaveAs(FileName, this.miss, this.miss, this.miss, this.miss, this.miss, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, this.miss, this.miss, this.miss, this.miss);
        }

        public void SetCellValue(string value)
        {
            if (this.m_Range == null)
            {
                throw new Exception("没有设定单元格或者区域");
            }
            this.m_Range.Value2 = value;
        }

        public void SetCellValue(int row, int col, string value)
        {
            this.SetRange(col, row);
            this.m_Range.Value2 = value;
        }

        public void SetCellValue(int startRow, int startCol, int endRow, int endCol, string value)
        {
            this.Merge(startRow, startCol, endRow, endCol);
            this.m_Range.Value2 = value;
        }

        public void SetColumnWidth(float columnWidth)
        {
            this.m_Range.ColumnWidth = columnWidth;
        }

        public void SetColumnWidth(int columnIndex, float columnWidth)
        {
            this.SetRange(columnIndex, 0);
            this.m_Range.ColumnWidth = columnWidth;
        }

        public void SetFont(string fontName)
        {
            this.m_Font.Name = fontName;
        }

        public void SetFont(int rowIndex, int columnIndex, int fontSize)
        {
            this.SetRange(columnIndex, rowIndex);
            this.m_Font.Size = fontSize;
        }

        public void SetFont(int rowIndex, int columnIndex, string fontName)
        {
            this.SetRange(columnIndex, rowIndex);
            this.m_Font.Name = fontName;
        }

        public void SetFont(int rowIndex, int columnIndex, string fontName, int fontSize)
        {
            this.SetRange(columnIndex, rowIndex);
            this.m_Font.Name = fontName;
            this.m_Font.Size = fontSize;
        }

        public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, int fontSize)
        {
            this.SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
            this.m_Font.Size = fontSize;
        }

        public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, string fontName)
        {
            this.SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
            this.m_Font.Name = fontName;
        }

        public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, string fontName, int fontSize)
        {
            this.SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
            this.m_Font.Name = fontName;
            this.m_Font.Size = fontSize;
        }

        public void SetHorizontal(eHorizontal eH)
        {
            if (this.m_Range == null)
            {
                throw new Exception("没有设定单元格或者区域");
            }
            switch (eH)
            {
                case eHorizontal.eLeft:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    break;

                case eHorizontal.eRight:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignRight;
                    break;

                case eHorizontal.eCenter:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    break;

                default:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    break;
            }
        }

        public void SetHorizontal(int rowIndex, int columnIndex, eHorizontal eH)
        {
            this.SetRange(columnIndex, rowIndex);
            switch (eH)
            {
                case eHorizontal.eLeft:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    break;

                case eHorizontal.eRight:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignRight;
                    break;

                case eHorizontal.eCenter:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    break;

                default:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    break;
            }
        }

        public void SetHorizontal(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, eHorizontal eH)
        {
            this.SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
            switch (eH)
            {
                case eHorizontal.eLeft:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    break;

                case eHorizontal.eRight:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignRight;
                    break;

                case eHorizontal.eCenter:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    break;

                default:
                    this.m_Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    break;
            }
        }

        public void SetRange(int ColNum, int RowNum)
        {
            this.m_Range = this.m_objSheet.get_Range(this.GetCell(ColNum, RowNum), this.miss);
            this.m_Font = this.m_Range.Font;
            this.m_Borders = this.m_Range.Borders;
            this.m_BorderTop = this.m_Borders[XlBordersIndex.xlEdgeTop];
            this.m_BorderBottom = this.m_Borders[XlBordersIndex.xlEdgeBottom];
            this.m_BorderLeft = this.m_Borders[XlBordersIndex.xlEdgeLeft];
            this.m_BorderRight = this.m_Borders[XlBordersIndex.xlEdgeRight];
            this.m_cellRange = this.m_Range;
        }

        public void SetRange(int ColNum, int RowNum, object oFormat)
        {
            this.m_Range = this.m_objSheet.get_Range(this.GetCell(ColNum, RowNum), this.miss);
            this.m_Font = this.m_Range.Font;
            this.m_Borders = this.m_Range.Borders;
            this.m_BorderTop = this.m_Borders[XlBordersIndex.xlEdgeTop];
            this.m_BorderBottom = this.m_Borders[XlBordersIndex.xlEdgeBottom];
            this.m_BorderLeft = this.m_Borders[XlBordersIndex.xlEdgeLeft];
            this.m_BorderRight = this.m_Borders[XlBordersIndex.xlEdgeRight];
            this.m_cellRange = this.m_Range;
            this.m_Range.Areas[0].NumberFormat = "[红色]-0.00";
        }

        public void SetRange(int startColNum, int startRowNum, int endColNum, int endRowNum)
        {
            this.m_Range = this.m_objSheet.get_Range(this.GetCell(startColNum, startRowNum), this.GetCell(endColNum, endRowNum));
            this.m_Font = this.m_Range.Font;
            this.m_Borders = this.m_Range.Borders;
            this.m_BorderTop = this.m_Borders[XlBordersIndex.xlEdgeTop];
            this.m_BorderBottom = this.m_Borders[XlBordersIndex.xlEdgeBottom];
            this.m_BorderLeft = this.m_Borders[XlBordersIndex.xlEdgeLeft];
            this.m_BorderRight = this.m_Borders[XlBordersIndex.xlEdgeRight];
            this.m_cellRange = this.m_Range;
        }

        public void SetRange(int startColNum, int startRowNum, int endColNum, int endRowNum, object oFormat)
        {
            this.m_Range = this.m_objSheet.get_Range(this.GetCell(startColNum, startRowNum), this.GetCell(endColNum, endRowNum));
            this.m_Font = this.m_Range.Font;
            this.m_Borders = this.m_Range.Borders;
            this.m_BorderTop = this.m_Borders[XlBordersIndex.xlEdgeTop];
            this.m_BorderBottom = this.m_Borders[XlBordersIndex.xlEdgeBottom];
            this.m_BorderLeft = this.m_Borders[XlBordersIndex.xlEdgeLeft];
            this.m_BorderRight = this.m_Borders[XlBordersIndex.xlEdgeRight];
            this.m_cellRange = this.m_Range;
            if (oFormat == null)
            {
                this.m_cellRange.NumberFormat = "@";
            }
            else
            {
                this.m_cellRange.NumberFormat = oFormat.ToString();
            }
        }

        public void SetRowHeigh(float rowHeigh)
        {
            this.m_Range.RowHeight = rowHeigh;
        }

        public void SetRowHeigh(int rowIndex, float rowHeigh)
        {
            this.SetRange(0, rowIndex);
            this.m_Range.RowHeight = rowHeigh;
        }

        public void UserControl(bool usercontrol)
        {
            if (this.m_objExcel != null)
            {
                this.m_objExcel.UserControl = usercontrol;
                this.m_objExcel.DisplayAlerts = usercontrol;
                this.m_objExcel.Visible = usercontrol;
            }
        }

        public enum eHorizontal
        {
            eLeft,
            eRight,
            eCenter
        }
    }
}

